SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[DISC_CDS_CreateRedemptionCodes]
	@RequestID int,
	@NetPrice decimal(12,8),
	@DateTime datetime,
	@MaxDownloads int,
	@StampVars nvarchar(max),
	@Promo nvarchar(255),
	@usersID nvarchar(255),
	@Password nvarchar(255)
AS
BEGIN
	DECLARE @OID nvarchar(10);
	DECLARE @DistID varchar(50);
	DECLARE @Identifier varchar(50);
	DECLARE @RedemptionCodes int;
	DECLARE @RequestDate datetime;
	DECLARE @ExpDate datetime;
	
	DECLARE @OrderItems table (OrderItemID int);
	DECLARE @Codes table(Code varchar(8));

	SELECT @RedemptionCodes = CodesRequested, @OID = OID, @DistID = DistributorID, @Identifier = Identifier, @ExpDate = ExpDate
	FROM dbo.[DnnProduct_CDS_Requests]
	WHERE ID = @RequestID;

	IF (@RedemptionCodes > 0) BEGIN	
		DELETE TOP(@RedemptionCodes)
		FROM dbo.[DnnProduct_CDS_RedemptionCodesAvailable]
		OUTPUT DELETED.code INTO @Codes;

		DECLARE @total int
		SELECT @total = COUNT(*) FROM @Codes;

		IF (@total < @RedemptionCodes)
		BEGIN
			RAISERROR ('There are not enough codes available.', 10, 1);
		END

		;WITH q (n) AS (
			SELECT 1
			UNION ALL
			SELECT n + 1
			FROM   q
			WHERE  n < @RedemptionCodes
		)
		INSERT INTO DISC_DistOrderItems (DistID, PermID, OrderID, NetPrice, DateTime, MaxDownloads, StampVars, Promo, usersID, Password)
		OUTPUT INSERTED.ID INTO @OrderItems
		SELECT @DistID, @Identifier, @OID, @NetPrice, @DateTime, @MaxDownloads, @StampVars, @Promo, @usersID, @Password FROM q
		OPTION (MAXRECURSION 0)

		;WITH T1 AS (SELECT Code, ROW_NUMBER() OVER (ORDER BY Code) AS RowNum FROM @Codes),
		T2 AS (SELECT OrderItemID, ROW_NUMBER() OVER (ORDER BY OrderItemID) AS RowNum FROM @OrderItems)
		INSERT INTO dbo.[DnnProduct_CDS_RedemptionCodes] (RedemptionCode, RequestID, ExpDate, DistOrderItemId)
		SELECT T1.Code, @RequestID, @ExpDate, T2.OrderItemID
		FROM T1 JOIN T2 ON T1.RowNum = T2.RowNum;

	END;

	SELECT RedemptionCode, DistOrderItemId
	FROM dbo.[DnnProduct_CDS_RedemptionCodes]
	WHERE RequestID = @RequestID
END
GO
